/*
	This proc gives the receive lots information on the day as well in between the date Ranges
	EXEC sp_wip_info_receive_report '0','0','0','0','01/01/2010','02/04/2010','asOfDat'
*/
ALTER PROCEDURE sp_wip_info_receive_report
(
	@PROCESS_TYPE		VARCHAR(50),
	@PROCESSSTEP_IDS	VARCHAR(100),
	@BASEPRODUCT_ID		VARCHAR(50),
	@VENDOR			    VARCHAR(100),
	@FROMDATE		    VARCHAR(50),
	@TODATE 		    VARCHAR(50),
	@REPORT_TYPE		VARCHAR(50)
)

 AS
 BEGIN
				
	CREATE TABLE #RESULT_TABLE ( [BASE PRODUCT] VARCHAR(50), [PART NUMBER] VARCHAR(50), [LOT NUMBER] VARCHAR(50),[FAB LOT NUMBER] VARCHAR(50),
						[DATE IN] DATETIME, [WAFER QTY] INT, [DIE QTY] INT, [VENDOR] VARCHAR(50),[PROCESS TYPE] VARCHAR(20), [RECEIVE DATE] DATETIME )
						
  	
	DECLARE @SQLSTRING	NVARCHAR(4000)
	DECLARE @SQLSTRING1	NVARCHAR(4000)
	DECLARE @EXCELEXECUTESTRING	NVARCHAR(4000)
	DECLARE @CRYSTALEXECUTESTRING	NVARCHAR(4000)
		
	SET @SQLSTRING = N' INSERT INTO #RESULT_TABLE
						SELECT (SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
					CASE 
						WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
						WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
					END 
				),
				P.PART_NUMBER, LOT_NUMBER,FAB_LOT_NUMBER, DATE_IN, ISNULL((SELECT MAX(Wafer_Qty_IN) FROM LOT_TABLE_HISTORY WHERE LOT_ID = LT.LOT_ID),Wafer_Qty_IN ),
				 ISNULL((SELECT MAX(QTY_IN) FROM LOT_TABLE_HISTORY WHERE LOT_ID = LT.LOT_ID), QTY_IN ), L_VENDOR_NAME, PS_TYPE,	LT.TIMESTAMP
				FROM PRODUCT P, PROCESS_STEP PS, LOCATION L, LOT_TABLE LT
				WHERE P.PROCESSSTEP_ID=PS.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID 
				AND LT.LOCATION_ID = L.LOCATION_ID '
	
	SET @SQLSTRING1 = ''
	
	IF ( @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND PS.PS_TYPE = ''' +  @PROCESS_TYPE + '''' 
	END
	ELSE
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND PS.PS_TYPE IN ( ''FAB'' ,''SORT'', ''Die BANK'',''Assembly'',''FT'',''FG'' ) '
	END
	
	IF ( @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.PROCESSSTEP_ID = ''' +  @PROCESSSTEP_IDS + '''' 
	END 

	IF ( @BASEPRODUCT_ID IS NOT NULL AND @BASEPRODUCT_ID <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND P_PARENT_PRODUCT_ID =  ''' +  @BASEPRODUCT_ID + '''' 
	END
	
	IF ( @VENDOR IS NOT NULL AND @VENDOR <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' and L.VENDOR_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING (  ''' + @VENDOR + '''' +') )'
	END
	IF @REPORT_TYPE = 'asOfDate'
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND CONVERT(VARCHAR,LT.TIMESTAMP,101) = '''+ CONVERT(VARCHAR,@TODATE, 101)+''''
	END
	ELSE
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@FROMDATE, 101)+''' AND '''+CONVERT(VARCHAR,@TODATE, 101)+''' '	
	END			
	
	PRINT @SQLSTRING + @SQLSTRING1

	EXEC (@SQLSTRING + @SQLSTRING1)
	
	SELECT * FROM #RESULT_TABLE Order BY [BASE PRODUCT], [PART NUMBER], [RECEIVE DATE]

END